package cn.ds.general.sql.util;

import cn.ds.general.sql.entity.CaseWhen;
import cn.ds.general.sql.entity.GroupBy;
import cn.ds.general.sql.entity.Sql;
import cn.ds.general.sql.entity.Union;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import org.jooq.*;
import org.jooq.impl.DefaultDSLContext;

import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.stream.Collectors;

import static org.jooq.impl.DSL.*;

public class SqlUtil {

    private static final DSLContext CONTEXT = new DefaultDSLContext(SQLDialect.DEFAULT);

    public static SelectQuery genSql(Sql sql) {
        if (sql == null) {
            throw new RuntimeException("sql is null");
        }

        cn.ds.general.sql.entity.Table tbl = sql.getTable();
        if (tbl == null) {
            throw new RuntimeException("table is null");
        }
        Table<Record> tableRecord;
        if (ObjectUtil.isNotNull(tbl.getSql())) {
            tableRecord = table(genSql(tbl.getSql()));
        } else {
            tableRecord = table(tbl.getName());
        }
        // 设置表别名
        if (StrUtil.isNotBlank(sql.getTable().getAlia())) {
            tableRecord.as(sql.getTable().getAlia());
        }

        SelectQuery select = CONTEXT.selectQuery();
        // 如果字段不为空，设置字段
        if (CollectionUtil.isNotEmpty(sql.getFields())) {
            List<cn.ds.general.sql.entity.Field> fields = sql.getFields();
            select.addSelect(fields.stream().map(f -> {
                Field<Object> fid;
                if (CollectionUtil.isNotEmpty(f.getCaseWhens())) {
                    List<CaseWhen> caseWhens = f.getCaseWhens();
                    CaseConditionStep<Object> when = null;
                    if (CollectionUtil.isNotEmpty(caseWhens)) {
                        for (CaseWhen cw : caseWhens) {
                            List<cn.ds.general.sql.entity.Condition> whenConditions = cw.getWhenConditions();
                            Condition condCaseWhen = generateCondition(whenConditions, cw.getAndOr());
                            if (when == null) {
                                when = when(condCaseWhen, convertType(cw.getThenValue(), cw.getType()));
                            } else {
                                when.when(condCaseWhen, convertType(cw.getThenValue(), cw.getType()));
                            }
                        }
                    }
                    assert when != null;
                    if (f.getCaseElse() != null) {
                        when.otherwise(f.getCaseElse());
                    }
                    fid = field(when);
                } else {
                    fid = field(f.getField());
                }

                if (StrUtil.isNotBlank(f.getAlia())) {
                    fid = fid.as(f.getAlia());
                }

                return fid;
            }).collect(Collectors.toList()));
        } else {
            select.addSelect(field("*"));
        }

        select.addFrom(tableRecord);

        if (CollectionUtil.isNotEmpty(sql.getJoins())) {
            sql.getJoins().forEach(j -> {
                Table<Record> t;
                if (ObjectUtil.isNotNull(j.getTable().getSql())) {
                    t = table(genSql(j.getTable().getSql()));
                } else {
                    t = table(j.getTable().getName());
                }

                if (StrUtil.isNotBlank(j.getTable().getAlia())) {
                    t.as(j.getTable().getAlia());
                }
                if ("left".equals(j.getType())) {
                    select.addJoin(t, JoinType.LEFT_OUTER_JOIN, generateCondition(j.getOn(), j.getAndOr()));
                }
                if ("right".equals(j.getType())) {
                    select.addJoin(t, JoinType.RIGHT_OUTER_JOIN, generateCondition(j.getOn(), j.getAndOr()));
                } else {
                    select.addJoin(t, JoinType.JOIN, generateCondition(j.getOn(), j.getAndOr()));
                }
            });
        }

        // 如果where条件不为空
        if (CollectionUtil.isNotEmpty(sql.getWhere())) {
            select.addConditions(generateCondition(sql.getWhere(), sql.getAndOr()));
        }

        // 设置分组
        if (BeanUtil.isNotEmpty(sql.getGroupBy())) {
            GroupBy sqlGroupBy = sql.getGroupBy();
            if (CollectionUtil.isNotEmpty(sqlGroupBy.getFields())) {
                select.addGroupBy(sqlGroupBy.getFields().stream().map(f -> field(f.getField())).collect(Collectors.toList()));
            }
            // 设置分组条件
            if (CollectionUtil.isNotEmpty(sqlGroupBy.getHaving())) {
                select.addHaving(generateCondition(sqlGroupBy.getHaving(), "and"));
            }
        }

        if (BeanUtil.isNotEmpty(sql.getLimit())) {
            if (sql.getLimit().getOffset() == null) {
                select.addLimit(sql.getLimit().getStart());
            } else {
                select.addLimit(sql.getLimit().getStart(), sql.getLimit().getOffset());
            }
        }

        // 设置排序
        if (CollectionUtil.isNotEmpty(sql.getOrderBy())) {
            select.addOrderBy(sql.getOrderBy().stream().map(f -> {
                Field<Object> fld = field(f.getName());
                if ("desc".equals(f.getOrder())) {
                    fld.desc();
                }
                return fld;
            }).collect(Collectors.toList()));
        }

        Union union = sql.getUnion();
        if (ObjectUtil.isNotNull(union)) {
            for (Sql s : union.getSqls()) {
                String type = union.getType();
                switch (type) {
                    case "union":
                        select.union(genSql(s));
                        break;
                    case "union all":
                        select.unionAll(genSql(s));
                        break;
                    case "intersect":
                        select.intersect(genSql(s));
                        break;
                    case "except":
                        select.except(genSql(s));
                        break;
                    default:
                        throw new RuntimeException("type is not support");
                }
            }
        }

        return select;
    }

    public static Condition generateCondition(List conditionList, String operator) {
        List<Condition> whereList = new ArrayList<>(16);
        for (Object c : conditionList) {
            // 判断条件是否为空，为空直接跳过
            if (ObjectUtil.isEmpty(c)) {
                continue;
            }
            if (JSONUtil.isTypeJSON(c.toString()) || c instanceof cn.ds.general.sql.entity.Condition) {
                cn.ds.general.sql.entity.Condition condition = BeanUtil.toBean(c, cn.ds.general.sql.entity.Condition.class);
                if ("and".equals(condition.getOperator()) || "or".equals(condition.getOperator())) {
                    whereList.add(generateCondition(condition.getValues(), condition.getOperator()));
                } else {
                    Object val = null;
                    SelectQuery selectQuery = null;
                    if (ObjectUtil.isNotNull(condition.getSql())) {
                        selectQuery = genSql(condition.getSql());
                    } else {
                        if (CollectionUtil.isEmpty(condition.getValues())) {
                            throw new IllegalArgumentException("Condition values cannot be empty.");
                        }
                        val = convertType(condition.getValues().get(0), condition.getDataType());
                    }
                    if (StrUtil.isBlank(condition.getOperator())) {
                        throw new IllegalArgumentException("Condition operator cannot be blank.");
                    }
                    switch (condition.getOperator().toLowerCase(Locale.ROOT)) {
                        case "<":
                            if (ObjectUtil.isNotNull(selectQuery)) {
                                whereList.add(field(condition.getField()).lt(selectQuery));
                            } else {
                                whereList.add(field(condition.getField()).lt(val));
                            }
                            break;
                        case "=":
                        case "==":
                        case "equal":
                        case "is":
                            if (ObjectUtil.isNotNull(selectQuery)) {
                                whereList.add(field(condition.getField()).eq(selectQuery));
                            } else {
                                whereList.add(field(condition.getField()).eq(val));
                            }
                            break;
                        case ">=":
                            if (ObjectUtil.isNotNull(selectQuery)) {
                                whereList.add(field(condition.getField()).ge(selectQuery));
                            } else {
                                whereList.add(field(condition.getField()).ge(val));
                            }
                            break;
                        case "<=":
                            whereList.add(field(condition.getField()).le(val));
                            break;
                        case ">":
                            if (ObjectUtil.isNotNull(selectQuery)) {
                                whereList.add(field(condition.getField()).gt(selectQuery));
                            } else {
                                whereList.add(field(condition.getField()).gt(val));
                            }
                            break;
                        case "<>":
                        case "!=":
                        case "not equal":
                        case "is not":
                            if (ObjectUtil.isNotNull(selectQuery)) {
                                whereList.add(field(condition.getField()).ne(selectQuery));
                            } else {
                                whereList.add(field(condition.getField()).ne(val));
                            }
                            break;
                        case "like":
                            whereList.add(field(condition.getField()).like(StrUtil.toString(val)));
                            break;
                        case "in":
                            if (ObjectUtil.isNotNull(selectQuery)) {
                                whereList.add(field(condition.getField()).in(selectQuery));
                            } else {
                                whereList.add(field(condition.getField()).in(condition.getValues()));
                            }
                            break;
                        case "null":
                        case "is unknown":
                            whereList.add(field(condition.getField()).isNull());
                            break;
                        case "not null":
                        case "is not unknown":
                            whereList.add(field(condition.getField()).isNotNull());
                            break;
                        case "not in":
                            if (ObjectUtil.isNotNull(selectQuery)) {
                                whereList.add(field(condition.getField()).notIn(selectQuery));
                            } else {
                                whereList.add(field(condition.getField()).notIn(condition.getValues()));
                            }
                            break;
                        case "between":
                            if (condition.getValues().size() != 2) {
                                throw new IllegalArgumentException("Between condition must have two values.");
                            }
                            whereList.add(field(condition.getField()).between(val, convertType(condition.getValues().get(1), condition.getDataType())));
                            break;
                            case "not between":
                            if (condition.getValues().size() != 2) {
                                throw new IllegalArgumentException("Not between condition must have two values.");
                            }
                            whereList.add(field(condition.getField()).notBetween(val, convertType(condition.getValues().get(1), condition.getDataType())));
                            break;
                        case "is true":
                        case "is not false":
                            whereList.add(field(condition.getField()).isTrue());
                            break;
                        case "is false":
                        case "is not true":
                            whereList.add(field(condition.getField()).isFalse());
                            break;
                        case "exists":
                            whereList.add(exists(selectQuery));
                            break;
                        case "not exists":
                            whereList.add(notExists(selectQuery));
                            break;
                        default:

                    }
                }
            }
        }
        if ("and".equals(operator)) {
            return and(whereList);
        } else {
            return or(whereList);
        }
    }

    public static Object convertType(Object value, String type) {
        // 检查值是否为空，如果为空则返回null
        if (ObjectUtil.isEmpty(value)) {
            return null;
        }
        String val = value.toString();
        // 检查类型是否为空，如果为空则根据值的类型进行转换
        if (StrUtil.isBlank(type)) {
            if (NumberUtil.isInteger(val)) {
                return Convert.toInt(value);
            } else if (NumberUtil.isDouble(val)) {
                return Convert.toDouble(value);
            } else if (NumberUtil.isLong(val)) {
                return Convert.toLong(value);
            } else {
                return value;
            }
        }

        // 根据指定的类型进行转换
        switch (type.toLowerCase(Locale.ROOT)) {
            case "int":
                return Convert.toInt(value);
            case "float":
                return Convert.toFloat(value);
            case "double":
                return Convert.toDouble(value);
            case "long":
                return Convert.toLong(value);
            case "date":
                return DateUtil.parseDate(val).toLocalDateTime();
            case "datetime":
                return DateUtil.parseDateTime(val).toLocalDateTime();
            case "timestamp":
                return DateUtil.parse(val);
            case "boolean":
            case "bool":
                return Convert.toBool(value);
            case "field":
                return field(val);
            default:
                return value;
        }
    }

}
